package com.sfac.day02.modules.test.dao;

import com.sfac.day02.modules.common.entity.SearchBean;
import com.sfac.day02.modules.test.entity.City;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@Mapper
public interface CityDao {
    //增加city
    @Insert("insert into " +
            "test_city(" +
            "city_name,local_city_name,country_id," +
            "district,population,date_modified,date_created" +
            ")" +
            "values(" +
            "#{cityName},#{localCityName},#{countryId}," +
            "#{district},#{population},#{dateModified},#{dateCreated}" +
            ")")
    @Options(useGeneratedKeys = true, keyColumn = "city_id", keyProperty = "cityId")
    public abstract void addCity(City city);
    //修改city
    @Update({"update test_city set city_name = #{cityName}, local_city_name=#{localCityName}," +
            " country_id = #{countryId}, district=#{district}, population=#{population}," +
            "date_modified=#{dateModified}, date_created=#{dateCreated} where city_id=#{cityId}"})
    public abstract void updateCity(City city);
    //通过id删除city
    @Delete("delete from test_city where city_id = #{cityId}")
    public abstract void deleteCityById(Integer cityId);
    //通过id查看city
    @Select("select * from test_city left join test_country on test_city.country_id = test_country.country_id " +
            "where test_city.city_id = #{cityId}")
    public abstract City selectCityByCityId(Integer cityId);
    //联表查询接口
    @Select("select ci.*,co.country_name from test_city ci"
                +"left join coutry co on ci.country_id=co.country_id"
                +"where ci.city_id=#{cityId}")
    City getCityByCityId(int cityId);
    //组合查询接口
    @Select("select * from test_city where country_id = #{countryId}")
    List<City> getgetCitiesByCountryId(int countryId);
    //多条件分页查询接口
    @Select("<script>"
            +"select ci.*,co.country_name from test_city ci left join country co"
            +"on ci.country_id = co.country_id"
            +"<where>"
            +"<if test='keyWord != \"\" and keyWord != null'>"
            +"and (ci.city_name like '%${keyWord}%'"
            +"or ci.local_city_name like '%{keyWord}%'"
            +"</if>"
            +"</where>"
            +"<choose>"
            + "<when test='orderBy != \"\" and orderBy != null'>"
            +" order by ${orderBy} ${direction}"
            + "</when>"
            + "<otherwise>"
            + " order by city_id desc"
            + "</otherwise>"
            + "</choose>"
            +"</script>")
    List<City> getgetCitiesBySearchBean(SearchBean searchBean);

}
